﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

using Bpms.Data;
using Bpms.Entity;
using Bpms.Log;
using Bpms.Config;

namespace Bpms.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region Private
        //当前文件对应的逻辑类名，用于写日志
        string categoryClassName = "Bpms.Data.SqlServer.BusinessCategory";
        //数据表名
        string categoryTableName = string.Format("[{0}business_category]", ConfigContext.GetTablePrefix);

        /// <summary>
        /// 绑定数据到类别实体
        /// </summary>
        /// <param name="idr">idatareader</param>
        /// <returns></returns>
        IList<BusinessCategoryInfo> GetDataBindedCategory(IDataReader idr)
        {
            IList<BusinessCategoryInfo> categoryList = new List<BusinessCategoryInfo>();

            while (idr.Read())
            {
                var category = new BusinessCategoryInfo();

                category.ID = (int)idr["business_category_id"];
                category.Name = idr["name"].ToString();
                category.OrganizationID = (int)idr["organization_id"];
                category.ParentID = (int)idr["parent_id"];
                category.ParentIDList = idr["parent_id_list"].ToString();
                category.Depth = Convert.ToByte(idr["depth"]);
                category.RootID = (int)idr["root_id"];
                category.ChildQuantity = (int)idr["child_quantity"];
                category.SortNumber = Convert.ToByte(idr["sort_number"]);
                category.IsDelete = Convert.ToBoolean(idr["is_delete"]);

                categoryList.Add(category);
            }

            return categoryList;
        }

        /// <summary>
        /// 根据sql语句返回类别信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        BusinessCategoryInfo GetCategoryBySQL(string sql)
        {
            var list = GetCategoryListBySQL(sql);

            if (list.Count > 0)
            {
                return list.First();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句返回类别列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        IList<BusinessCategoryInfo> GetCategoryListBySQL(string sql)
        {
            IList<BusinessCategoryInfo> categoryList = new List<BusinessCategoryInfo>();

            using (var idr = DbHelper.ExecuteReader(CommandType.Text, sql) as IDataReader)
            {
                try
                {
                    categoryList = GetDataBindedCategory(idr);
                }
                catch (Exception e)
                {
                    log.WriteLog(this.categoryClassName + ":获取绑定的类别信息时出现异常", e);
                }
                finally
                {
                    if (idr != null)
                    {
                        idr.Close();
                    }
                }
            }

            return categoryList;
        }

        /// <summary>
        /// 根据ID返回当前类别下的最大排序号
        /// </summary>
        /// <param name="businessCategoryId">当前类别ID</param>
        /// <returns></returns>
        byte GetBusinessCategoryMaxSortNumber(int businessCategoryId)
        {

            var sql = string.Format("SELECT MAX([sort_number]) FROM {0} WHERE ", this.categoryTableName);

            if (businessCategoryId == 0)
            {
                sql += "[parent_id] = 0";
            }
            else
            {
                sql += string.Format("[business_category_id] = {0}", businessCategoryId);
            }
            try
            {
                var result = (byte)DbHelper.ExecuteScalar(CommandType.Text, sql);
                return result;
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName + "：获取业务类别最大排序号时发生异常", e);
                throw;
            }
        }

        /// <summary>
        /// 根据ID返回当前类别的深度
        /// </summary>
        /// <param name="businessCategoryId">当前类别ID</param>
        /// <returns></returns>
        int GetDepth(int businessCategoryId)
        {
            //如果是虚拟根类别
            if (businessCategoryId == 0)
            {
                return 0;
            }

            var sql = string.Format("SELECT [depth] FROM {0} WHERE [business_category_id] = {1}", this.categoryTableName, businessCategoryId);

            try
            {
                var result = (int)DbHelper.ExecuteScalar(CommandType.Text, sql);
                return result;
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName + "：获取深度时发生异常", e);
                throw;
            }
        }

        /// <summary>
        /// 根据父类别ID生成当前类别的parent_id_list
        /// </summary>
        /// <param name="parentId">父类别ID</param>
        /// <returns></returns>
        string GetParentIdList(int parentId)
        {
            //如果是虚拟根类别
            if (parentId == 0)
            {
                return "0";
            }

            var sql = string.Format("SELECT [parent_id_list]+','+[business_category_id] FROM {0} WHERE [business_category_id] = {1}", this.categoryTableName, parentId);

            try
            {
                var result = DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
                return result;
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName + "：获取parent_id_list时发生异常", e);
                throw;
            }
        }
        #endregion

        public bool InsertBusinessCategory(BusinessCategoryInfo businessCategory)
        {
            try
            {
                businessCategory.SortNumber = (byte)(GetBusinessCategoryMaxSortNumber(businessCategory.ParentID) + 1);
                businessCategory.Depth = (byte)(GetDepth(businessCategory.ParentID) + 1);
                businessCategory.ParentIDList = GetParentIdList(businessCategory.ParentID);
                businessCategory.RootID = 0;
                businessCategory.ChildQuantity = 0;
            }
            catch
            {
                return false;
            }
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 100, businessCategory.Name), 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, businessCategory.OrganizationID), 
                DbHelper.MakeInParam("@parent_id", (DbType)SqlDbType.Int, 4, businessCategory.ParentID), 
                DbHelper.MakeInParam("@parent_id_list", (DbType)SqlDbType.VarChar, 200, businessCategory.ParentIDList), 
                DbHelper.MakeInParam("@depth", (DbType)SqlDbType.TinyInt, 1, businessCategory.Depth), 
                DbHelper.MakeInParam("@root_id", (DbType)SqlDbType.Int, 4, businessCategory.RootID), 
                DbHelper.MakeInParam("@child_quantity", (DbType)SqlDbType.Int, 4, businessCategory.ChildQuantity), 
                DbHelper.MakeInParam("@sort_number", (DbType)SqlDbType.TinyInt, 1, businessCategory.SortNumber),
                DbHelper.MakeInParam("@sort_number", (DbType)SqlDbType.TinyInt, 1, 0)
            };
            try
            {
                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_add_business_category", parms) > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {

                log.WriteLog(this.categoryClassName, e);
            }

            return false;
        }

        public bool UpdateBusinessCategory(BusinessCategoryInfo businessCategory)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 100, businessCategory.Name), 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, businessCategory.OrganizationID), 
                DbHelper.MakeInParam("@new_parent_id", (DbType)SqlDbType.Int, 4, businessCategory.ParentID), 
                DbHelper.MakeInParam("@business_category_id", (DbType)SqlDbType.Int, 4, businessCategory.ID) 
            };

            try
            {
                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_update_business_category", parms) > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
            }

            return false;
        }

        public int IsBusinessCategoryDeleteable(int businessCategoryId)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, businessCategoryId) 
            };
            var sql = string.Format("SELECT 1 FROM {0} WHERE [child_quantity] > 0 AND [business_category_id] = @id", this.categoryTableName);

            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) != null)
                {   //本类别下含有子类别
                    return -1;
                }
                else
                {
                    sql = string.Format("SELECT 1 FROM {0} WHERE [parent_id] = 0 AND [business_category_id] = @id AND (SELECT COUNT(*) FROM {0} WHERE [parent_id] = 0 AND [is_delete] =0 ) < 2 ", this.categoryTableName);
                    if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) != null)
                    {   //根类别子类别数小于两个
                        return -2;
                    }

                    return 1;
                }
            }
            catch (Exception e)
            {   //异常
                log.WriteLog(this.categoryClassName, e);
                return 0;
            }
        }

        public bool DeleteBusinessCategory(int businessCategoryId, bool isDeleteRalation)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@business_category_id", (DbType)SqlDbType.Int, 4, businessCategoryId), 
                DbHelper.MakeInParam("@is_delete_relation", (DbType)SqlDbType.Int, 4, isDeleteRalation ? 1 : 0) 
            };

            try
            {
                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_delete_business_category", parms) > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
            }

            return false;
        }

        public bool BusinessCategoryExists(BusinessCategoryInfo businessCategory, OperateType type)
        {
            var sql = string.Format("SELECT 1 FROM {0} WHERE [is_delete] = 0 AND ", this.categoryTableName);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, businessCategory.ID), 
                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 100, businessCategory.Name) 
            };

            switch (type)
            {
                case OperateType.Insert:
                    sql += "[name] = @name";
                    break;
                case OperateType.Update:
                    sql += "[name] = @name AND [business_category_id] <> @id";
                    break;
                default:
                    log.WriteLog(this.categoryClassName + ":检测业务类别是否存在，参数错误，无指定的检测类型，将按照已经存在处理，防止添加新类别");
                    sql += "1 = 1";
                    break;
            }
            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) != null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
            }

            return false;
        }

        public BusinessCategoryInfo GetBusinessCategory(int businessCategoryId)
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [business_category_id] = {1} AND [is_delete] = 0", this.categoryTableName, businessCategoryId);
            return GetCategoryBySQL(sql);
        }

        public IList<BusinessCategoryInfo> GetAllBusinessCategories()
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [is_delete] = 0 ORDER BY [sort_number] ASC", this.categoryTableName);
            return GetCategoryListBySQL(sql);
        }

        public IList<BusinessCategoryInfo> GetBusinessCategories(int parentId)
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [parent_id] = {1}  AND [is_delete] = 0 ORDER BY [sort_number] ASC", this.categoryTableName, parentId);
            return GetCategoryListBySQL(sql);
        }

        public bool IsBusinessCategorySortable(int businessCategoryId, byte sortNumber)
        {
            var sql = string.Format("SELECT 1 FROM {0} WHERE [parent_id] = (SELECT [parent_id] FROM {0} WHERE [business_category_id]= @categoryId) AND [is_delete] = 0 GROUP BY [parent_id] HAVING  MAX([sort_number]) < @sortNumber OR MIN([sort_number]) > @sortNumber ", this.categoryTableName);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@categoryId", (DbType)SqlDbType.Int, 4, businessCategoryId), 
                DbHelper.MakeInParam("@sortNumber", (DbType)SqlDbType.TinyInt, 1, sortNumber),
            };

            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) == null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName + "检测业务类别是否可以排序时出现异常，返回不能排序标识，防止排序", e);
            }

            return false;
        }

        public bool SetCategorySortNumber(int businessCategoryId, int sortNumber)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, businessCategoryId), 
                DbHelper.MakeInParam("@new_sort_number", (DbType)SqlDbType.Int, 4, sortNumber) 
            };

            try
            {
                var result = DbHelper.ExecuteScalar(CommandType.StoredProcedure, "bpms_set_category_sort_number", parms);
                return Convert.ToBoolean(result);
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
                return false;
            }
        }

        public string GetBusinessCategoryNameById(int businessCategoryId)
        {
            var sql = string.Format("SELECT [name] FROM {0} WHERE [business_category_id] = {1}  AND [is_delete] = 0", this.categoryTableName, businessCategoryId);

            try
            {
                return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
                return string.Empty;
            }
        }

        public int GetBusinessCategoryIdByName(string name)
        {
            var sql = string.Format("SELECT [business_category_id] FROM {0} WHERE [name] = '{1}'  AND [is_delete] = 0", this.categoryTableName, name);

            try
            {
                return (int)DbHelper.ExecuteScalar(CommandType.Text, sql);
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
                return -1;
            }
        }
    }
}
